package cn.edu.cug.cs.gtl.gsm.jdbc.oracle;

import cn.edu.cug.cs.gtl.common.Identifier;
import cn.edu.cug.cs.gtl.gsm.item.IndexItem;
import cn.edu.cug.cs.gtl.util.StringUtils;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class IndexTable {
    private String INDEX_TABLE_NAME;
    private String SQL_DROP_INDEX_TABLE;
    private String SQL_CREATE_INDEX_TABLE;
    private String SQL_DELETE_INDEX_TABLE_DATA;
    private String SQL_SELECT_INDEX_TABLE_DATA;
    private String SQL_SELECT_ALL_INDEX_TABLE_DATA;
    private String SQL_DELETE_ALL_INDEX_TABLE_DATA;
    private String SQL_INSERT_INDEX_TABLE_DATA;
    private String SQL_UPDATE_INDEX_TABLE_DATA;
    private String SQL_SELECT_FOR_UPDATE_INDEX_TABLE_DATA;

    protected Connection connection;


    public IndexTable(Connection connection,String baseName) {
        this.connection = connection;
        INDEX_TABLE_NAME=baseName+".rdb_sm_index_"+baseName;
        SQL_DROP_INDEX_TABLE= "DROP TABLE "+INDEX_TABLE_NAME;
        SQL_CREATE_INDEX_TABLE= "CREATE TABLE " + INDEX_TABLE_NAME + " ( page_identifier  VARCHAR2(255) PRIMARY KEY, index_item CLOB)";
        SQL_DELETE_INDEX_TABLE_DATA= "DELETE FROM " + INDEX_TABLE_NAME + " WHERE page_identifier = ?";
        SQL_SELECT_INDEX_TABLE_DATA= "SELECT index_item FROM " + INDEX_TABLE_NAME + " WHERE page_identifier = ?";
        SQL_SELECT_ALL_INDEX_TABLE_DATA= "SELECT index_item FROM " + INDEX_TABLE_NAME ;
        SQL_DELETE_ALL_INDEX_TABLE_DATA= "DELETE FROM " + INDEX_TABLE_NAME ;
        SQL_INSERT_INDEX_TABLE_DATA= "INSERT INTO " + INDEX_TABLE_NAME + " VALUES (?,?)";
        SQL_UPDATE_INDEX_TABLE_DATA= "UPDATE " + INDEX_TABLE_NAME + " SET index_item = empty_clob() WHERE page_identifier=?";
        SQL_SELECT_FOR_UPDATE_INDEX_TABLE_DATA= "SELECT index_item FROM " + INDEX_TABLE_NAME + " WHERE page_identifier = ? FOR UPDATE ";
    }


    public boolean create() throws SQLException {
        Statement statement = connection.createStatement();
        boolean r =  statement.execute(SQL_CREATE_INDEX_TABLE);
        statement.close();
        return r;
    }

    public boolean drop() throws SQLException {
        if(exists()) {
            Statement statement = connection.createStatement();
            boolean r = statement.execute(SQL_DROP_INDEX_TABLE);
            statement.close();
            return r;
        }
        else
            return true;
    }

    public boolean delete(Identifier pageIdentifier) throws SQLException{
        PreparedStatement statement = connection.prepareStatement(SQL_DELETE_INDEX_TABLE_DATA);
        statement.setString(1,pageIdentifier.toString());
        boolean r =  statement.execute();
        statement.close();
        return r;
    }

    public boolean deleteAll() throws SQLException{
        Statement statement = connection.createStatement();
        boolean r =  statement.execute(SQL_DELETE_ALL_INDEX_TABLE_DATA);
        statement.close();
        return r;
    }

    public boolean insert(IndexItem item) throws SQLException, IOException {
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT_INDEX_TABLE_DATA);
        statement.setString(1,item.getExternalPageIdentifier().toString());
        Clob b = connection.createClob();
        b.setString(1,item.storeToCompressString());
        statement.setClob(2,b);
        boolean r =  statement.execute();
        statement.close();
        return r;
    }

    public boolean update(IndexItem item) throws SQLException, IOException {
        PreparedStatement statement = connection.prepareStatement(SQL_UPDATE_INDEX_TABLE_DATA);
        statement.setString(1,item.getExternalPageIdentifier().toString());
        statement.executeUpdate();
        statement.close();
        statement = connection.prepareStatement(SQL_SELECT_FOR_UPDATE_INDEX_TABLE_DATA);
        statement.setString(1,item.getExternalPageIdentifier().toString());
        ResultSet rs = statement.executeQuery();
        if(rs.next()){
            Clob b = rs.getClob(1);
            b.setString(1,item.storeToCompressString());
            statement.executeUpdate();
        }
        rs.close();
        statement.close();
        return true;
    }

    public IndexItem select(Identifier externalPageIdentifier) throws SQLException, IOException {
        PreparedStatement statement = connection.prepareStatement(SQL_SELECT_INDEX_TABLE_DATA);
        statement.setString(1,externalPageIdentifier.toString());
        ResultSet r =  statement.executeQuery();
        Clob  compressZip = null;
        while(r.next()){
            compressZip = r.getClob(1);
            if(compressZip!=null) {
                IndexItem ids = new IndexItem();
                ids.loadFromCompressString(compressZip.getSubString(1,(int)compressZip.length()));
                r.close();
                statement.close();
                return ids;
            }
            else {
                r.close();
                statement.close();
                return null;
            }
        }
        r.close();
        statement.close();
        return null;
    }

    public List<IndexItem> selectAll() throws SQLException, IOException {
        PreparedStatement statement = connection.prepareStatement(SQL_SELECT_ALL_INDEX_TABLE_DATA);
        ResultSet r =  statement.executeQuery();
        List<IndexItem> ls=new ArrayList<>();
        while(r.next()){

            Clob compressZip = null;
            compressZip = r.getClob(1);
            if(compressZip!=null) {
                IndexItem ids = new IndexItem();
                ids.loadFromCompressString(compressZip.getSubString(1,(int)compressZip.length()));
                ls.add( ids);
            }
        }
        r.close();
        statement.close();
        return ls;
    }

    public boolean exists() throws SQLException{
        String []sa = StringUtils.split(INDEX_TABLE_NAME,".");
        return OracleStorageManager.exists(connection,sa[0],sa[1]);
    }

}
